package com.delete.action;

import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import com.delete.executors.ExecutorFactory;
import com.delete.executors.MExecutor;
import com.delete.sql.SqlCreate;
import com.kevin.cache.CacheManager;
import com.kevin.enums.DBType;
import com.kevin.exceptions.ExceptionFactory;
import com.kevin.sql.SqlFormat;
import com.kevin.table.MCellStruct;
import com.kevin.table.MDataColumn;
import com.kevin.table.MDataRow;
import com.kevin.utils.StaticTool;

public class TableSchemaHandler {
	public static String getExecutorName(Object tableNames)
    {
        String executorName = StringUtils.EMPTY;
        if (tableNames instanceof Enum)
        {
//            Type t = tableNames.GetType();
//            String enumName = t.Name;
//            if (enumName != "TableNames" && enumName != "ViewNames")
//            {
//                if (enumName.Length > 1 && enumName[1] == '_')
//                {
//                    executorName = enumName.Substring(2, enumName.Length - 6);//.Replace("Enum", "Conn");
//                }
//                else
//                {
//                    String[] items = t.FullName.Split('.');
//                    if (items.Length > 1)
//                    {
//                        executorName = items[items.Length - 2];// +"Conn";
//                        items = null;
//                    }
//                }
//            }
//            t = null;
        }
        else if (tableNames instanceof String)
        {
            String tName = tableNames.toString();
            int index = tName.lastIndexOf(')');
            
        	//(select * from aaa) ddd.v_xxx
            if (index > 0) // 视图
            {
                String a = tName.substring(0, index + 1);//a部分
                tName = tName.substring(index + 1).trim();//b部分。ddd.v_xxx
                //修改原对像

                if (tName.contains("."))
                {
                	//(select * from aaa)+""+v_xxx
                    tableNames = a + " " + tName.substring(tName.lastIndexOf('.') + 1);
                }
            }
            
            //executorName.tableName
            if (tName.contains(".") && !tName.trim().contains(" "))
            {
            	executorName = tName.split(".")[0];
            }

        }
        return executorName;
    }
	
    /**
     * 获取主键列名
     * @param tableName
     * @return
     * @throws SQLException 
     */
    private static Map<String,Boolean> getPrimaryKeys(DatabaseMetaData metaData,String tableName) throws SQLException{
    	Map<String,Boolean> primaryMap = new HashMap<>();
        ResultSet rs = metaData.getPrimaryKeys(null, null, tableName);
        while (rs.next()){ 
        	primaryMap.put(rs.getString("COLUMN_NAME"),true);
        }
        return primaryMap;
    }
    
    /**
     * 获取表的外键
     * @param metaData
     * @param tableName
     * @return<外键列名称，外键表名称>
     * @throws SQLException
     */
    private static Map<String,String> getForeignKeys(DatabaseMetaData metaData,String tableName) throws SQLException{
    	Map<String,String> foreignMap = new HashMap<>();
    	ResultSet rs = metaData.getImportedKeys(null, null, tableName);
        while (rs.next()){ 
        	foreignMap.put(rs.getString("FKCOLUMN_NAME"),rs.getString("PKTABLE_NAME"));
        }
        return foreignMap;
    }
    
    private static Map<String,Boolean> getUniqueKeys(DatabaseMetaData metaData,String tableName) throws SQLException{
    	Map<String,Boolean> uniqueMap = new HashMap<>();
    	ResultSet rs = metaData.getIndexInfo(null, null, tableName,true,true);
        while (rs.next()){ 
        	uniqueMap.put(rs.getString("COLUMN_NAME"),!rs.getBoolean("NON_UNIQUE"));
        }
        return uniqueMap;
    }
    
	/**
	 * 判断主键
	 * @param tableName
	 * @param columnName
	 * @return
	 * @throws SQLException
	 */
	public boolean isPrimaryKey(DatabaseMetaData metaData,String tableName,String columnName) throws SQLException {
		Map<String,Boolean> pKeys = getPrimaryKeys(metaData,tableName);
		return pKeys.get(columnName) == null?false:true;
	}
	
	
	
	/**
	 * 获取数据表和备注
	 * @return
	 * @throws SQLException
	 */
	public static Map<String, String> getTables(DatabaseMetaData metaData){
		Map<String, String> tableMap = new HashMap<>();
		String[] types = { "TABLE","VIEW" };  
		try {
			//数据库名称，数据库登录名，表名称，类型标准（"TABLE"、"VIEW"、"SYSTEM TABLE"、"GLOBAL TEMPORARY"、"LOCAL TEMPORARY"、"ALIAS" 和 "SYNONYM"）
			ResultSet rs = metaData.getTables(null, null, null, types);
	        while (rs.next()) {  
	            String tableName = rs.getString("TABLE_NAME");  //表名  
	            //String tableType = rs.getString("TABLE_TYPE");  //表类型  
	            String remarks = rs.getString("REMARKS");       //表备注  
	            tableMap.put(tableName, remarks);
	        }
		} catch (SQLException e) {
			throw ExceptionFactory.Exception("get tableinfo failed !");
		}  
        return tableMap;
	}
	
	
	
	public static Map<String, String> getTables(MExecutor executor)
    {
		return getTables(executor.getMetaData());
    }
	
    public static MDataRow fillTableSchema(MDataRow row, MExecutor executor, String tableName, String sourceTableName)
    {
//        if (FillSchemaFromCache(ref row, ref dbBase, tableName, sourceTableName))
//        {
//            return true;
//        }
//        else//从Cache加载失败
//        {
            return fillSchemaFromDb(row, executor, tableName, sourceTableName);
//        }
    }
	
	private static MDataRow fillSchemaFromDb(MDataRow row, MExecutor executor, String tableName, String sourceTableName)
    {
        try
        {
            MDataColumn mdcs = null;
            //if (tableName.IndexOf('(') > -1 && tableName.IndexOf(')') > -1)//自定义视图table
            //{
            //    dbBase.tempSql = "view";//使用access方式加载列
            //}
            mdcs = getColumns(tableName, executor);
            if (mdcs.size() == 0)
            {
            	throw ExceptionFactory.Exception(new Exception(tableName + "is not exist!"));
            }
            row = mdcs.toRow(sourceTableName);
            row.setTableName(sourceTableName);
//            String key = GetSchemaKey(tableName, dbBase.DataBase, dbBase.dbType);
            //CacheManage.LocalInstance.Set(key, mdcs.Clone(), 1440);

//            switch (dbBase.dbType)//文本数据库不保存。
//            {
//                //case DBType.Access:
//                //case DBType.SQLite:
//                //case DBType.MsSql:
//                //case DBType.MySql:
//                //case DBType.Oracle:
//
//                case DBType.Txt:
//                case DBType.Xml:
//                    break;
//                default:
//                    if (!String.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
//                    {
//                        String folderPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath;
//                        if (System.IO.Directory.Exists(folderPath))
//                        {
//                            mdcs.WriteSchema(folderPath + key + ".ts");
//                        }
//                    }
//                    break;
//            }
            return row;

        }
        catch (Exception err)
        {
            //Log.WriteLogToTxt(err);
        	throw ExceptionFactory.Exception("tableName or view"+tableName+" not exist!",err);
        }
    }
	
	/**
	 * 获取所有列
	 * @param metaData
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	private static Map<String, Map<String, Object>> getColumns(DatabaseMetaData metaData,String tableName) throws SQLException{
        Map<String,Map<String,Object>> columnProps = new HashMap<>();
        Map<String,Object> colProp = null;
        ResultSet rs = metaData.getColumns(null, null, tableName, null);
        Map<String,Boolean> pKeys = getPrimaryKeys(metaData,tableName);
        Map<String, String> fKeys = getForeignKeys(metaData, tableName);
        Map<String, Boolean> uniKeys = getUniqueKeys(metaData, tableName);
        while(rs.next()){
            colProp = new HashMap<>();
            colProp.put("sqlType",rs.getInt("DATA_TYPE"));//对应的java.sql.Types的SQL类型(列类型ID)  
            colProp.put("description",rs.getString("REMARKS"));//列描述
            colProp.put("defaultValue",rs.getString("COLUMN_DEF"));//默认值  
            colProp.put("isCanNull",rs.getInt("NULLABLE") == 0?false:true);//是否允许为null
            colProp.put("isAutoIncrement",rs.getBoolean("IS_AUTOINCREMENT"));//指示此列是否是自动递增
            colProp.put("columnName",rs.getString("COLUMN_NAME"));//列名
            colProp.put("tableName",rs.getString("TABLE_NAME"));  //表名    
            colProp.put("maxSize",rs.getInt("COLUMN_SIZE"));//列大小 
            colProp.put("scale",rs.getInt("DECIMAL_DIGITS")); //小数位数   
            colProp.put("isPrimaryKey", pKeys.get(colProp.get("columnName")) == null?false:true);
            colProp.put("sqlTypeName",rs.getString("TYPE_NAME"));//java.sql.Types类型名称(列类型名称)  
            colProp.put("isForeignKey",fKeys.containsKey(colProp.get("columnName")));
            colProp.put("fkTableName",fKeys.get(colProp.get("columnName")));
            colProp.put("isUniqueKey",uniKeys.containsKey(colProp.get("columnName"))?uniKeys.get(colProp.get("columnName")):false);
            columnProps.put((String)colProp.get("columnName"),colProp);
        }   
        return columnProps;
    }
	
	/**
	 * 获取所有列
	 * @param tableName
	 * @param dbHelper
	 * @return
	 * @throws SQLException
	 */
	public static MDataColumn getColumns(String tableName, MExecutor executor) throws SQLException
    {
		MDataColumn mdcs = new MDataColumn();
		boolean isView = tableName.contains(" ");
		if(!isView){
			isView = exists("V", tableName, executor);
		}
		if(isView){
			mdcs = getViewColumns(tableName, executor);
		}else{
			tableName = String.valueOf(SqlCreate.sqlToViewSql(tableName));
			tableName = SqlFormat.keyword(tableName, executor.getDbType());		
			MCellStruct mStruct = null;		
			mdcs.setDbType(executor.getDbType());
			Map<String, Map<String, Object>> colInfo = getColumns(executor.getMetaData(),tableName);
			Map<String, Object> colProp = null;
			for(String key : colInfo.keySet()){
				colProp = colInfo.get(key);
				mStruct = new MCellStruct(mdcs.getDbType());
				mStruct.setColumnName((String)colProp.get("columnName"));
				mStruct.setSqlType((int)colProp.get("sqlType"));
				mStruct.setDescription((String)colProp.get("description"));;
				mStruct.setDefaultValue((String)colProp.get("defaultValue"));
				mStruct.setCanNull((boolean)colProp.get("isCanNull"));
				mStruct.setAutoIncrement((boolean)colProp.get("isAutoIncrement"));
				mStruct.setTableName((String)colProp.get("tableName"));
				mStruct.setMaxSize((int)colProp.get("maxSize"));;
				mStruct.setScale((int)colProp.get("scale"));;
				mStruct.setPrimaryKey((boolean)colProp.get("isPrimaryKey"));
				mStruct.setTypeName((String)colProp.get("sqlTypeName"));
				mStruct.setForeignKey((boolean)colProp.get("isForeignKey"));
				mStruct.setFkTableName((String)colProp.get("fkTableName"));
				mStruct.setUniqueKey((boolean)colProp.get("isUniqueKey"));
				mdcs.add(mStruct);
			}
		}
				
		return mdcs;
    }

	private static MDataColumn getViewColumns(String sqlText, MExecutor executor) throws SQLException{
		executor.open();
		PreparedStatement pstmt = executor.getPreparedStatement(sqlText);
		ResultSetMetaData metaData = pstmt.getMetaData();
		MDataColumn mdc = getColumns(metaData);
		mdc.setDbType(executor.getDbType());
		return mdc;		
	}
	
	private static MDataColumn getColumns(ResultSetMetaData metaData) throws SQLException{
		int columnCount = metaData.getColumnCount();
		if(columnCount == 0) return null;
		MCellStruct mStruct = null;
		MDataColumn mdcs = new MDataColumn();
		for (int i = 0; i < columnCount; i++) {
			String columnName = metaData.getColumnName(i);
			int sqlType = metaData.getColumnType(i);
			boolean isAutoIncrement = metaData.isAutoIncrement(i);
			boolean isCanNull = metaData.isNullable(i) == ResultSetMetaData.columnNullable;
			int maxSize = metaData.getColumnDisplaySize(i);
			mStruct = new MCellStruct(columnName,sqlType,isAutoIncrement,isCanNull,maxSize);
			mStruct.setScale(metaData.getScale(i));
			mStruct.setTypeName(metaData.getColumnTypeName(i));
			mStruct.setTableName(metaData.getTableName(i));
			mdcs.add(mStruct);
		}
		return mdcs;
	}
	
	public static String getSchemaKey(String tableName, String dbName, DBType dbType) {
		String key = tableName;
        int start = key.indexOf('(');
        int end = key.lastIndexOf(')');
        if (start > -1 && end > -1)//自定义table
        {
            key = "View" + StaticTool.getHashKey(key);
        }
        else
        {
            if (key.indexOf('.') > 0)
            {
                dbName = StringUtils.split(".")[0];
            }
            key = SqlFormat.notKeyword(key);
        }
        return "ColumnsCache_" + dbType + "_" + dbName + "_" + key;
	}
	
	/**
	 * 映射的表名
	 * @param executorName
	 * @param tableName
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static String getMapTableName(String executorName, String tableName)
    {
		Map<String,String> mapTable = null;
		String key = "MapTalbe:" + executorName.hashCode();
		if(CacheManager.getLocalInstance().contains(key)){
			Object temp = CacheManager.getLocalInstance().get(key);
			mapTable = temp == null ? null : (Map<String, String>)temp;
		}else{
			Map<String, String> tableNameMap = getTables(executorName);
			if(tableNameMap != null && tableNameMap.size()>0){
				mapTable = new HashMap<>();
				String mapName = StringUtils.EMPTY;
				for (String item : tableNameMap.keySet()) {
					mapName = item.replace("_", "").replace("-", "").replace(" ", "");		
					if(!mapTable.containsKey(mapName)){
						mapTable.put(mapName.toLowerCase(), item);
					}
				}
				CacheManager.getLocalInstance().set(key, mapTable,1440);
			}
		}
		key = tableName.replace("_", "").replace("-", "").replace(" ", "").toLowerCase();
		key = SqlFormat.notKeyword(key);
        if (mapTable != null && mapTable.size() > 0 && mapTable.containsKey(key))
        {
            return mapTable.get(key);
        }
        return tableName;
    }
	
	public static Map<String, String> getTables(String executorName)
    {
		MExecutor executor = ExecutorFactory.getExecutor(executorName);
        //helper.IsAllowRecordSql = false;
        //dbName = helper.DataBase;
//        if (!helper.TestConn(AllowConnLevel.MaterBackupSlave))
//        {
//            if (String.IsNullOrEmpty(errInfo))
//            {
//                //errInfo = "Open database fail : " + dbName;
//            }
//            helper.Dispose();
//            return null;
//        }
        Map<String, String> tables = TableSchemaHandler.getTables(executor);//内部有缓存
        return tables;
    }

	
    private final static String EXIST2000 = "SELECT count(*) FROM sysobjects where id = OBJECT_ID(N'{0}') AND xtype in (N'{1}')";
    private final static String EXIST2005 = "SELECT count(*) FROM sys.objects where object_id = OBJECT_ID(N'{0}') AND type in (N'{1}')";
    private final static String EXISTORACLE = "Select count(*)  From user_objects where object_type='{0}' and object_name=upper('{1}')";
    private final static String EXISTMYSQL = "SELECT count(*)  FROM  `information_schema`.`COLUMNS`  where TABLE_NAME='{0}' and TABLE_SCHEMA='{1}'";
    private final static String EXISTSYBASE = "SELECT count(*) FROM sysobjects where id = OBJECT_ID(N'{0}') AND type in (N'{1}')";
    private final static String EXISTSQLLITE = "SELECT count(*) FROM sqlite_master where type='{0}' and name='{1}'";
	
	public static boolean exists(String type, String name, MExecutor executor) throws SQLException
    {
//        if (type == "U" && tableCache.Count > 0)
//        {
//            String key = GetTableCacheKey(helper);
//            if (tableCache.ContainsKey(key))
//            {
//                return tableCache[key].ContainsKey(name);
//            }
//        }
        int result = 0;
        String exist = StringUtils.EMPTY;
        //executor.IsAllowRecordSql = false;
        DBType dbType = executor.getDbType();
        name = SqlFormat.keyword(name, executor.getDbType());
        switch (dbType)
        {
            case ACCESS:
//                try
//                {
//                    System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(helper.Con.ConnectionString);
//                    con.Open();
//                    DataTable dt = null;
//                    if (type == "U")
//                    {
//                        dt = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, SqlFormat.NotKeyword(name), "Table" });
//                    }
//                    else if (type == "V")
//                    {
//                        dt = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Views, new object[] { null, null, SqlFormat.NotKeyword(name) });
//                    }
//                    if (dt != null)
//                    {
//                        result = dt.Rows.Count;
//                    }
//                    con.Close();
//                }
//                catch (Exception err)
//                {
//                    Log.WriteLogToTxt(err);
//                }
                break;
            case MYSQL:
                if (type != "V" || (type == "V" && name.toLowerCase().startsWith("v_")))//视图必须v_开头
                {
                    exist = String.format(EXISTMYSQL, SqlFormat.notKeyword(name), executor.getMetaData().getSchemaTerm());
                }
                break;
            case ORACLE:
                exist = String.format(EXISTORACLE, (type == "U" ? "TABLE" : "VIEW"), name);
                break;
            case MSSQL:
                exist = String.format(executor.getMetaData().getDatabaseProductVersion().startsWith("08") ? EXIST2000 : EXIST2005, name, type);
                break;
            case SQLITE:
                exist = String.format(EXISTSQLLITE, (type == "U" ? "table" : "view"), SqlFormat.notKeyword(name));
                break;
            case SYBASE:
                exist = String.format(EXISTSYBASE, SqlFormat.notKeyword(name), type);
                break;
            case TXT:
            case XML:
//                String folder = helper.Con.DataSource + Path.GetFileNameWithoutExtension(name);
//                FileInfo info = new FileInfo(folder + ".ts");
//                result = (info.Exists && info.Length > 10) ? 1 : 0;
//                if (result == 0)
//                {
//                    info = new FileInfo(folder + (dbType == DBType.Txt ? ".txt" : ".xml"));
//                    result = (info.Exists && info.Length > 10) ? 1 : 0;
//                }
                break;
		default:
			break;
        }
        if (exist != StringUtils.EMPTY)
        {
			ResultSet resultSet = executor.query(exist);
			try {
				if(resultSet.next()){
					result = resultSet.getInt(1);
				}
			} catch (SQLException e) {
				e.printStackTrace();
				throw ExceptionFactory.Exception(e);
			}
        }
        return result > 0;
    }
}
